import re
import shutil
import sqlite3
import os
from config import ConfigManager

def parse_scp(spc_fullname):
    '''
    解析spc.csv文件
    return: file_info,c
    '''
    config_manager = ConfigManager('config.ini')
    paths = config_manager.get_options('PATH')
    
    # 获取文件名各段信息
    # spc_fullname  SPC@NZFM08-X3A-011-12@182323027903050000000206@6@20240112112320.csv 
    # spc_fullname 是  fullname，获取其中的 path  和 filename(不包括文件扩展名)
    spc_file = os.path.basename(spc_fullname)
    path = os.path.dirname(spc_fullname)
    
    file_info = spc_file.split('.')[0].split('@')
    file_info = {
        'ProgramName':file_info[1],
        'BoardID':file_info[2],
        'LotNo':file_info[3],
        'SpcTime':file_info[4]
    }
    
    #把文件里的第2行的值名作为字典的key，第3行的值名作为字典的value
    # 打开文件
    scp=''
    try:
        scp = open(spc_fullname,'r',encoding='utf8') 
    except Exception as ex:
        scp = open(spc_fullname,'r',encoding='gbk')
    scp.readline()
    keys = scp.readline().strip().split(',')
    # 移除 keys 里的空值，保持顺序
    keys = [key for key in keys if key.strip()]
    vals = scp.readline().strip().split(',')[0:len(keys)]
    scp_dict = dict(zip(keys,vals))
    
    # 定义一个字符串，只包括 windows合法文件名符号以外的字符
    illegal_str = r'[\\/:*?"<>|+]'
    
    # scp_dict['BoardID'] 判断BoardID是否 包括 illegal_str这里的字符，是就抛出异常
    if re.search(illegal_str,scp_dict['BoardID']):
        raise Exception('BoardID包含非法字符,BoardID is illegal')
    
    # 打开一个sqlite内存库，以keys为各列名，建立 名为spc的表
    conn = sqlite3.connect(':memory:')
    c = conn.cursor()
    try:
        c.execute('create table head (%s)' % ','.join(keys))
        c.execute('INSERT INTO head VALUES (%s)' % ','.join(['?'] * len(vals)), vals)
    except Exception as ex:
        raise Exception("处理head 出错：" + str(ex))
    line=''
    for line in scp:
        if line.startswith('RefNo'):
            break
    else:
        raise Exception('No RefNo')
    
    spc_keys = line.strip().split(',')
    spc_keys = [key for key in spc_keys if key.strip()]
    # 以spc_keys为各列名，建立 名为spc的表
    try:
        sql = 'create table spc (%s)' % ','.join(spc_keys)
        c.execute(sql)
        for line in scp:
            values = line.strip().split(',')
            # 如果 AutoResult 为3 则忽略这一行
            if values[16]=='3':
                continue
            # 如果values[17]为1"gn" 则 修改values[32] 为新格式
            if values[17] == '1' and values[32]:
                oldFile = values[32]
                ext = values[32].split('.')[-1]
                # 获取 values[32] 这个fullname的路径
                path =  os.path.dirname(values[32])
                values[32] =path+"/" + scp_dict['ProgramName'] + scp_dict['BoardID'] + values[14] + values[0] + "." + ext # 程序名+条码+NgReason+RefNo
                newFile = values[32]
                shutil.copy(scp_dict['ImagePath']+"/"+values[32],paths['ppath']+"/"+newFile)
                print("复制图片：",paths['spath']+"/"+values[32],"  到  ",paths['ppath']+"/"+newFile)
                    
            sql = 'INSERT INTO spc VALUES (%s)' % ','.join(['?'] * len(values))
            c.execute(sql, values)
    except Exception as ex:
        raise Exception("处理 spc行 出错："+str(ex))
        
    scp.close()
    
    return file_info,c

def ngRows(cols,c):
    '''
    获取ng行
    '''
    # sql = "select * from spc where OPJudge='1'"
    sql = "select "+cols+"  from spc,head where OPJudge='1'"
    try:
        c.execute(sql)
        rows = c.fetchall()
        return rows
    except Exception as ex:
        # 把ex 的错误信息存到一个字符串里
        return "查找ng出错" + str(ex)
    
def okRows(cols,c):
    '''
    获取ok行
    '''
    sql = "select "+cols+" from spc ,head where BlockNo not in (select distinct BlockNo from spc where OPJudge='1') group by BlockNo "
    try:
        c.execute(sql)
        rows = c.fetchall()
        return rows
    except Exception as ex:
        # 把ex 的错误信息存到一个字符串里
        return "查找ok出错" + str(ex)
    
if __name__ == '__main__':
    file_info,c = parse_scp(r'spc.csv/SPC@NT4380-41@182424041056050000000009@4@20240415043256.csv')
    r = ngRows('',c)
    print(r)